import SQLite
import Foundation



class DatabaseManager {
    static let shared = DatabaseManager()
    
    private var db: Connection?
    
    // Table and columns
    private let translations = Table("translations")
    private let id = Expression<Int64>("id")
    private let beforeLanguage = Expression<String>("beforeLanguage")
    private let afterLanguage = Expression<String>("afterLanguage")
    private let beforeText = Expression<String>("beforeText")
    private let afterText = Expression<String>("afterText")
    private let isFavorite = Expression<Bool>("isFavorite")
    
    private init() {
        do {
            // Create or open the database
            let documentDirectory = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil as URL?, create: true)
            let fileUrl = documentDirectory.appendingPathComponent("translations").appendingPathExtension("sqlite3")
            db = try Connection(fileUrl.path)
            
            // Create the table if it doesn't exist
            try createTable()
        } catch {
            print(error)
        }
    }
    
    private func createTable() throws {
        guard let db = db else { return }
        do {
            try db.run(translations.create(ifNotExists: true) { table in
                table.column(id, primaryKey: .autoincrement)
                table.column(beforeLanguage)
                table.column(afterLanguage)
                table.column(beforeText)
                table.column(afterText)
                table.column(isFavorite)
            })
            print("Table created successfully")
        } catch {
            print(error)
        }
    }
    
    
    func insertTranslation(beforeLanguage: String, afterLanguage: String, beforeText: String, afterText: String, isFavorite: Bool) -> Int {
        guard let db = db else { return -1 }
        do {
            let insert = translations.insert(
                self.beforeLanguage <- beforeLanguage,
                self.afterLanguage <- afterLanguage,
                self.beforeText <- beforeText,
                self.afterText <- afterText,
                self.isFavorite <- isFavorite
            )
            let rowID = try db.run(insert)
            print("Translation inserted successfully with ID: \(rowID)")
            return Int(rowID)
        } catch {
            print(error)
            return -1
        }
    }
    
    
    func getAllTranslationsByisFavorite() -> [Translation] {
        guard let db = db else { return [] }
        var translationsList = [Translation]()
        do {
            // 按 id 降序排列查询结果，且 isFavorite 为 false
            for translation in try db.prepare(translations.filter(!isFavorite).order(id.desc)) {
                let translationItem = Translation(
                    id: translation[id],
                    beforeLanguage: translation[beforeLanguage],
                    afterLanguage: translation[afterLanguage],
                    beforeText: translation[beforeText],
                    afterText: translation[afterText],
                    isFavorite: translation[isFavorite]
                )
                translationsList.append(translationItem)
            }
        } catch {
            print(error)
        }
        return translationsList
    }

    
    
    // Fetch all translations
    func getAllTranslations() -> [Translation] {
        guard let db = db else { return [] }
        var translationsList = [Translation]()
        do {
            // 按 id 降序排列查询结果
            for translation in try db.prepare(translations.order(id.desc)) {
                let translationItem = Translation(
                    id: translation[id],
                    beforeLanguage: translation[beforeLanguage],
                    afterLanguage: translation[afterLanguage],
                    beforeText: translation[beforeText],
                    afterText: translation[afterText],
                    isFavorite: translation[isFavorite]
                )
                translationsList.append(translationItem)
            }
        } catch {
            print(error)
        }
        return translationsList
    }

    // Fetch a translation by ID
    func getTranslation(byId translationId: Int64) -> Translation {
        let translation = Translation(id: -1, beforeLanguage: "-1", afterLanguage: "-1", beforeText: "-1", afterText: "-1", isFavorite: false)
        guard let db = db else { return translation }
        do {
            if let translation = try db.pluck(translations.filter(id == translationId)) {
                return Translation(
                    id: translation[id],
                    beforeLanguage: translation[beforeLanguage],
                    afterLanguage: translation[afterLanguage],
                    beforeText: translation[beforeText],
                    afterText: translation[afterText],
                    isFavorite: translation[isFavorite]
                )
            }
        } catch {
            print(error)
        }
        return translation
    }
    
    // Update a translation
    func updateTranslation(translationId: Int64, newBeforeLanguage: String, newAfterLanguage: String, newBeforeText: String, newAfterText: String, newIsFavorite: Bool) {
        guard let db = db else { return }
        let translation = translations.filter(id == translationId)
        do {
            let update = translation.update(self.beforeLanguage <- newBeforeLanguage, self.afterLanguage <- newAfterLanguage, self.beforeText <- newBeforeText, self.afterText <- newAfterText, self.isFavorite <- newIsFavorite)
            try db.run(update)
            print("Translation updated successfully")
        } catch {
            print(error)
        }
    }
    
    // Delete a translation by ID
    func deleteTranslation(byId translationId: Int64) {
        guard let db = db else { return }
        let translation = translations.filter(id == translationId)
        do {
            try db.run(translation.delete())
            print("Translation deleted successfully")
        } catch {
            print(error)
        }
    }
    
    func getFavoriteTranslations() -> [Translation] {
        guard let db = db else { return [] }
        var favoriteTranslations = [Translation]()
        do {
            // 按 id 降序排列查询结果，并且过滤 isFavorite 为 true 的记录
            for translation in try db.prepare(translations.filter(isFavorite == true).order(id.desc)) {
                let translationItem = Translation(
                    id: translation[id],
                    beforeLanguage: translation[beforeLanguage],
                    afterLanguage: translation[afterLanguage],
                    beforeText: translation[beforeText],
                    afterText: translation[afterText],
                    isFavorite: translation[isFavorite]
                )
                favoriteTranslations.append(translationItem)
            }
        } catch {
            print(error)
        }
        return favoriteTranslations
    }

    
    // Toggle the isFavorite value for a given id
    func toggleFavoriteStatus(forId translationId: Int64) {
        guard let db = db else { return }
        let translation = translations.filter(id == translationId)
        do {
            if let currentTranslation = try db.pluck(translation) {
                let newIsFavorite = !currentTranslation[isFavorite]
                let update = translation.update(self.isFavorite <- newIsFavorite)
                try db.run(update)
                print("Translation favorite status toggled successfully")
            }
        } catch {
            print(error)
        }
    }
}
